//
//  SQLiteReader.m
//  WOPrototype
//
//  Created by Olof Millberg on 2010-04-15.
//  Copyright 2010 __MyCompanyName__. All rights reserved.
//

#import "SQLiteReader.h"
#import "WOPrototypeAppDelegate.h"
#import "WorkOrderPreview.h"
#import "WOAddress.h"
#import "WOFile.h"
#import "WOPerson.h"
#import "WOTimeReport.h"
#import "WOTimeInterval.h"
#import "WOHistoryEntry.h"
#import "WOChecklistPoint.h"
#import "WorkOrder.h"

@implementation SQLiteReader

#pragma mark Initialization Methods

/* Initialize with the databse file to use */
- (id) initWithDatabase:(NSString *)databaseName
{
	if (self = [super init])
	{
		[self checkAndCreateDatabase: databaseName];
		if(sqlite3_open([databasePath UTF8String], &database) != SQLITE_OK)
		{
			return nil;
		}
	}
	return self;
}

/* Set up the database file to use */
-(void) checkAndCreateDatabase:(NSString *)databaseName
{
	//Get file path to the database.
	NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    databasePath = [documentsDirectory stringByAppendingPathComponent:databaseName];
	
	NSError *error;
	NSFileManager *fileManager = [NSFileManager defaultManager];
	BOOL success = [fileManager fileExistsAtPath:databasePath];
	
	//Has the database been created in the file system?
	if(success) 
		return; //Yes, do nothing.
	else //No, copy it there.	
	{
		NSString *databasePathFromApp = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:databaseName];
		success = [fileManager copyItemAtPath:databasePathFromApp toPath:databasePath error:&error];
		if(!success)
			NSAssert1(0, @"Failed to create writable database file with message: '%@'.", [error localizedDescription]);
	}
	[fileManager release];
}

/* Deallocate stuff */
-(void) dealloc
{
	sqlite3_close(database);
	[super dealloc];
}

#pragma mark -

#pragma mark Helper Methods

/* Set parameters for filteringa work order preview */
- (void) setFilteringParameters:(WorkOrderPreview *)aWorkOrderPreview
{
	//Get employees, work leaders and current user.
	NSMutableArray *employees = [self getEmployeesByWorkID:aWorkOrderPreview.workID];
	NSMutableArray *workLeaders = [self getWorkLeadersByWorkID:aWorkOrderPreview.workID];
	WOPrototypeAppDelegate *appDelegate = (WOPrototypeAppDelegate *)[[UIApplication sharedApplication] delegate];
	WOPerson *currentUser = appDelegate.currentUser;
	
	BOOL assigned = NO;
	//Does current user exist in the assigned employees?
	for(WOPerson *currentEmployee in employees)
	{
		if([currentUser.socialID isEqualToString:currentEmployee.socialID])
		{
			assigned = YES;
			aWorkOrderPreview.assigned = YES;
			break;
		}
	}
	
	//Does current user exist in the assigned work leaders?
	if(!assigned)
	{
		for(WOPerson *currentWorkLeader in workLeaders)
		{
			if([currentUser.socialID isEqualToString:currentWorkLeader.socialID])
			{
				aWorkOrderPreview.assigned = YES;
				break;
			}
		}
	}
	
	//Is the work order not assigned to anyone?
	if([employees count] == 0 && [workLeaders count] == 0)
		aWorkOrderPreview.notAssignedToAnyone = YES;
	else
		aWorkOrderPreview.notAssignedToAnyone = NO;	
}

/* Check if a WOPerson exist */
- (BOOL) doesPersonExist:(WOPerson *)aNewPerson
{
	BOOL exists = NO;
	
	//Create the statement.
	NSString *sqlQuery = [NSString stringWithFormat: @"SELECT * FROM people WHERE id = '%@'",
						  aNewPerson.socialID];
	const char *sqlStatement = [sqlQuery UTF8String];
	sqlite3_stmt *compiledStatement;
	
	//Handle errors.
	if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK)
	{
		//Perform the statement.
		while(sqlite3_step(compiledStatement) == SQLITE_ROW)
		{
			exists = YES;
		}
		
	}
	//Release the compiled statement from memory.
	sqlite3_finalize(compiledStatement);
	
	return exists;
}

/* Get all info about a WOPerson given his/her social ID */
- (WOPerson *) getPersonBySocialID:(NSString *)aSocialID
{
	//Create parameters to fill with data.
	NSString *anID;
	NSString *aFirstName;
	NSString *aLastName;
	NSString *anEmail;
	NSString *aPhone;
	
	//Create the statement.
	NSString *sqlQuery = [NSString stringWithFormat: @"SELECT * FROM people WHERE id = '%@'", aSocialID];
	const char *sqlStatement = [sqlQuery UTF8String];
	sqlite3_stmt *compiledStatement;
	
	//Handle errors.
	if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK)
	{
		//Loop through the returned rows.
		while(sqlite3_step(compiledStatement) == SQLITE_ROW)
		{
			anID = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement,0)];
			aFirstName = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement,1)];
			aLastName = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement,2)];
			anEmail = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement,3)];
			aPhone = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement,4)];
		}
	}
	
	//Initialize the selected WOPerson.
	WOPerson *selectedPerson = [[WOPerson alloc] initWithID:anID firstName:aFirstName lastName:aLastName 
													  email:anEmail phone:aPhone];
	
	//Release the compiled statement from memory.
	sqlite3_finalize(compiledStatement);
	
	[selectedPerson autorelease];
	
	return selectedPerson;
}

/* Get the address if it exists */
- (WOAddress *) getAddressIfExists:(WOAddress *) anAddress
{
	//Create parameters to fill with data.
	WOAddress *addressToReturn = nil;
	NSString *aStreet = anAddress.street;
	NSString *aZipCode = anAddress.zipCode;
	NSString *anArea = anAddress.area;
	NSString *aCity = anAddress.city;
	
	//Create the statement.
	NSString *sqlQuery = [NSString stringWithFormat: @"SELECT * FROM address WHERE street = '%@' AND zip = '%@'", anAddress.street, anAddress.zipCode];
	const char *sqlStatement = [sqlQuery UTF8String];
	sqlite3_stmt *compiledStatement;
	
	//Handle errors.
	if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK)
	{
		//Loop through the returned rows.
		while(sqlite3_step(compiledStatement) == SQLITE_ROW)
		{
			aStreet = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement,0)];
			aZipCode = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement,1)];
			anArea = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement,2)];
			aCity = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement,3)];
		}
	}
	
	addressToReturn = [[WOAddress alloc] initWithStreet:aStreet zipCode:aZipCode area:anArea city:aCity];
	[addressToReturn autorelease];
	
	//Release the compiled statement from memory.
	sqlite3_finalize(compiledStatement);
	
	return addressToReturn;
}

#pragma mark -

#pragma mark Getter Methods

/* Get all time types */
- (NSMutableArray *) getTimeTypes
{
	//Init the array for storing read data.
	NSMutableArray *timeTypes = [[NSMutableArray alloc] init];
	
	//Create the statement.
	const char *sqlStatement = "SELECT * FROM timeType";
	sqlite3_stmt *compiledStatement;
	
	//Handle errors.	
	if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK)
	{
		//Loop through the returned rows.
		while(sqlite3_step(compiledStatement) == SQLITE_ROW)
		{
			NSString *currentTimeType = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 0)];
			
			[timeTypes addObject:currentTimeType];
		}
	}
	//Release the compiled statement from memory.
	sqlite3_finalize(compiledStatement);
	
	[timeTypes autorelease];
	
	return timeTypes;	
}

/* Get all priority types */
- (NSMutableArray *) getPriorityTypes
{
	//Init the array for storing read data.
	NSMutableArray *priorityTypes = [[NSMutableArray alloc] init];
	
	//Create the statement.
	const char *sqlStatement = "SELECT * FROM priority";
	sqlite3_stmt *compiledStatement;
	
	//Handle errors.	
	if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK)
	{
		//Loop through the returned rows.
		while(sqlite3_step(compiledStatement) == SQLITE_ROW)
		{
			NSString *currentPriorityType = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 0)];
			
			[priorityTypes addObject:currentPriorityType];
		}
	}
	//Release the compiled statement from memory.
	sqlite3_finalize(compiledStatement);
	
	[priorityTypes autorelease];
	
	return priorityTypes;
}

/* Get all status types */
- (NSMutableArray *) getStatusTypes
{
	//Init the array for storing read data.
	NSMutableArray *statusTypes = [[NSMutableArray alloc] init];
	
	//Create the statement.
	const char *sqlStatement = "SELECT * FROM status";
	sqlite3_stmt *compiledStatement;
	
	//Handle errors.	
	if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK)
	{
		//Loop through the returned rows.
		while(sqlite3_step(compiledStatement) == SQLITE_ROW)
		{
			NSString *currentStatusType = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 0)];
			[statusTypes addObject:currentStatusType];
		}
	}
	//Release the compiled statement from memory.
	sqlite3_finalize(compiledStatement);
	
	[statusTypes autorelease];
	
	return statusTypes;
}

/* Get all people for the addressbook */
- (NSMutableArray *) getAddressBookEntries
{
	//Init the array for storing read data.
	NSMutableArray *addressBookEntries = [[NSMutableArray alloc] init];
	
	//Create the statement.
	const char *sqlStatement = "SELECT * FROM people";
	sqlite3_stmt *compiledStatement;
	
	//Handle errors.	
	if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK)
	{
		//Loop through the returned rows.
		while(sqlite3_step(compiledStatement) == SQLITE_ROW)
		{
			//Read the data from the current row.
			NSString *aSocialID = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 0)];
			NSString *aFirstName = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 1)];
			NSString *aLastName = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 2)];
			NSString *aMail = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 3)];
			NSString *aPhone = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 4)];
				
			//Create, store and release a new instance of AddressBookEntry.
			WOPerson *currentPerson = [[WOPerson alloc] initWithID:aSocialID 
														 firstName:aFirstName 
														  lastName:aLastName 
															 email:aMail 
															 phone:aPhone];
			[addressBookEntries addObject:currentPerson];
			[currentPerson release];
		}
	}
	//Release the compiled statement from memory.
	sqlite3_finalize(compiledStatement);
	
	[addressBookEntries autorelease];
	
	return addressBookEntries;
}

/* Get previews of all available work order */
- (NSMutableArray *) getWorkOrderPreviewList
{	
	//Init the array for storing read data.
	NSMutableArray *workOrderPreviews = [[NSMutableArray alloc] init];

	//Create the statement.
	const char *sqlStatement = "SELECT * FROM workorder";
	sqlite3_stmt *compiledStatement;
	
	//Handle errors.
	if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK)
	{
		//Loop through the returned rows.
		while(sqlite3_step(compiledStatement) == SQLITE_ROW)
		{
			//Read the data from the current row.
			NSInteger aWorkID = sqlite3_column_int(compiledStatement, 0);
			NSString *aDescription = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 1)];
			NSString *anEntryDate = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 2)];
			NSString *anEndDate = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 3)];
			NSString *aStatus = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 5)];
			NSString *aPriority = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 6)];
			NSString *aStreet = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 8)];
			NSString *aZipCode = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 9)];
			NSString *anArea = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 10)];
			NSString *aCity = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 11)];
			
			WOAddress *currentAddress = [[WOAddress alloc] initWithStreet:aStreet zipCode:aZipCode area:anArea city:aCity];
			
			//NSString to NSDate formatting.
			NSDateFormatter *myDateFormatter = [[NSDateFormatter alloc] init];
			[myDateFormatter setDateFormat:@"yyyy-MM-dd HH:mm:SS"];
			NSDate *theEntryDate = [myDateFormatter dateFromString: anEntryDate];
			NSDate *theEndDate = [myDateFormatter dateFromString: anEndDate];
			[myDateFormatter release];
			
			//Create, store and release a new instance of WorkOrderPreviewCell.
			WorkOrderPreview *currentWorkOrderPreview = [[WorkOrderPreview alloc] initWithWorkID:aWorkID description:aDescription 
				status:aStatus priority:aPriority address:currentAddress entryDate:theEntryDate endDate:theEndDate];
					
			[workOrderPreviews addObject:currentWorkOrderPreview];
			[currentWorkOrderPreview release];
			[currentAddress release];
		}
	}
	//Release the compiled statement from memory.
	sqlite3_finalize(compiledStatement);
	
	[workOrderPreviews autorelease];
	
	return workOrderPreviews;
}

/* Get previews of all work orders not assigned to anyone */
- (NSMutableArray *) getAssignedWorkOrderPreviews
{
	//Get the current user's social ID.
	WOPrototypeAppDelegate *appDelegate = (WOPrototypeAppDelegate *)[[UIApplication sharedApplication] delegate];
	NSString *socialID = appDelegate.currentUser.socialID;
	
	//Init the array for storing read data.
	NSMutableArray *workOrderPreviews = [[NSMutableArray alloc] init];
	
	//Create the statement.
	NSString *sqlQuery = [NSString stringWithFormat:@"SELECT * FROM workorder wo WHERE EXISTS (SELECT * FROM hasEmployees he WHERE wo.id = he.workorder AND he.employee = '%@') AND wo.status != 'Avslutad'",
		socialID];
	const char *sqlStatement = [sqlQuery UTF8String];
	sqlite3_stmt *compiledStatement;
	
	//Handle errors.
	if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK)
	{
		//Loop through the returned rows.
		while(sqlite3_step(compiledStatement) == SQLITE_ROW)
		{
			//Read the data from the current row.
			NSInteger aWorkID = sqlite3_column_int(compiledStatement, 0);
			NSString *aDescription = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 1)];
			NSString *anEntryDate = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 2)];
			NSString *anEndDate = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 3)];
			NSString *aStatus = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 5)];
			NSString *aPriority = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 6)];
			NSString *aStreet = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 8)];
			NSString *aZipCode = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 9)];
			NSString *anArea = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 10)];
			NSString *aCity = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 11)];
			
			WOAddress *currentAddress = [[WOAddress alloc] initWithStreet:aStreet zipCode:aZipCode area:anArea city:aCity];
			
			//NSString to NSDate formatting.
			NSDateFormatter *myDateFormatter = [[NSDateFormatter alloc] init];
			[myDateFormatter setDateFormat:@"yyyy-MM-dd HH:mm:SS"];
			NSDate *theEntryDate = [myDateFormatter dateFromString: anEntryDate];
			NSDate *theEndDate = [myDateFormatter dateFromString: anEndDate];
			[myDateFormatter release];
			
			//Create, store and release a new instance of WorkOrderPreviewCell.
			WorkOrderPreview *currentWorkOrderPreview = [[WorkOrderPreview alloc] initWithWorkID:aWorkID description:aDescription 
																						  status:aStatus priority:aPriority address:currentAddress entryDate:theEntryDate endDate:theEndDate];
			
			[workOrderPreviews addObject:currentWorkOrderPreview];
			[currentWorkOrderPreview release];
			[currentAddress release];
		}
	}
	//Release the compiled statement from memory.
	sqlite3_finalize(compiledStatement);
	
	[workOrderPreviews autorelease];
	
	return workOrderPreviews;
}

/* Get previews of all work orders assigned to the current user */
- (NSMutableArray *) getUnassignedWorkOrderPreviews
{
	//Init the array for storing read data.
	NSMutableArray *workOrderPreviews = [[NSMutableArray alloc] init];
	
	//Create the statement.
	const char *sqlStatement = "SELECT * FROM workorder wo WHERE NOT EXISTS (SELECT * FROM hasEmployees he WHERE wo.id = he.workorder) AND wo.status !='Avslutad'";
	sqlite3_stmt *compiledStatement;
	
	//Handle errors.
	if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK)
	{
		//Loop through the returned rows.
		while(sqlite3_step(compiledStatement) == SQLITE_ROW)
		{
			//Read the data from the current row.
			NSInteger aWorkID = sqlite3_column_int(compiledStatement, 0);
			NSString *aDescription = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 1)];
			NSString *anEntryDate = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 2)];
			NSString *anEndDate = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 3)];
			NSString *aStatus = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 5)];
			NSString *aPriority = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 6)];
			NSString *aStreet = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 8)];
			NSString *aZipCode = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 9)];
			NSString *anArea = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 10)];
			NSString *aCity = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 11)];
			
			WOAddress *currentAddress = [[WOAddress alloc] initWithStreet:aStreet zipCode:aZipCode area:anArea city:aCity];
			
			//NSString to NSDate formatting.
			NSDateFormatter *myDateFormatter = [[NSDateFormatter alloc] init];
			[myDateFormatter setDateFormat:@"yyyy-MM-dd HH:mm:SS"];
			NSDate *theEntryDate = [myDateFormatter dateFromString: anEntryDate];
			NSDate *theEndDate = [myDateFormatter dateFromString: anEndDate];
			[myDateFormatter release];
			
			//Create, store and release a new instance of WorkOrderPreviewCell.
			WorkOrderPreview *currentWorkOrderPreview = [[WorkOrderPreview alloc] initWithWorkID:aWorkID description:aDescription 
																						  status:aStatus priority:aPriority address:currentAddress entryDate:theEntryDate endDate:theEndDate];
			
			[workOrderPreviews addObject:currentWorkOrderPreview];
			[currentWorkOrderPreview release];
			[currentAddress release];
		}
	}
	//Release the compiled statement from memory.
	sqlite3_finalize(compiledStatement);
	
	[workOrderPreviews autorelease];
	
	return workOrderPreviews;
}

/* Get the selected work order */
- (WorkOrder *) getWorkOrderByWorkID:(NSInteger)anID
{	
	//Initialize objects to fill with data later.
	NSInteger aWorkID;
	NSString *aDescription;
	NSString *aChecklistType;
	NSString *anEntryDate;
	NSString *anEndDate;
	NSString *aCustomer;			
	NSString *aStatus;
	NSString *aPriority;
	NSString *aStreet;
	NSString *aZipCode;
	NSString *anArea;
	NSString *aCity;
	NSMutableArray *someEmployees;
	NSMutableArray *someContactPersons;
	NSMutableArray *someWorkLeaders;
	NSMutableArray *someAttachedFiles;
	NSMutableArray *someHistoryEntries;			
	NSMutableArray *someTimeReports;
	NSMutableArray *someChecklistPoints;
	NSMutableArray *theTimeTypes;
	NSMutableArray *theStatusTypes;
	NSMutableArray *thePriorityTypes;
	
	//Create the statement.
	NSString *sqlQuery = [NSString stringWithFormat:@"SELECT * FROM workorder WHERE id = %i", anID];
	const char *sqlStatement = [sqlQuery UTF8String];
	sqlite3_stmt *compiledStatement;
	
	//Handle errors.
	if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK)
	{
		//Loop through the returned rows.
		while(sqlite3_step(compiledStatement) == SQLITE_ROW)
		{
			//Read the data from the current row.
			aWorkID = sqlite3_column_int(compiledStatement, 0);
			aDescription = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 1)];
			anEntryDate = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 2)];
			anEndDate = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 3)];
			aCustomer = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 4)];			
			aStatus = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 5)];
			aPriority = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 6)];
			aChecklistType = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 7)];
			aStreet = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 8)];
			aZipCode = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 9)];
			anArea = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 10)];
			aCity = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 11)];
		}
	}
	//Release the compiled statement from memory.
	sqlite3_finalize(compiledStatement);
	
	//NSStrin to NSDate formatting.
	NSDateFormatter *myDateFormatter = [[NSDateFormatter alloc] init];
	[myDateFormatter setDateFormat:@"yyyy-MM-dd HH:mm:SS"];
	NSDate *theEntryDate = [myDateFormatter dateFromString: anEntryDate];
	NSDate *theEndDate = [myDateFormatter dateFromString: anEndDate];
	[myDateFormatter release];
	
	//Initialize address.
	WOAddress *anAddress = [[WOAddress alloc] initWithStreet:aStreet zipCode:aZipCode area:anArea city:aCity];
	
	//Get data from other tables.
	someEmployees = [self getEmployeesByWorkID:anID];
	someContactPersons = [self getContactPersonsByWorkID:anID];
	someWorkLeaders = [self getWorkLeadersByWorkID:anID];
	someAttachedFiles = [self getAttachedFilesByWorkID:anID];
	someHistoryEntries = [self getHistoryEntriesByStreet:anAddress.street zipCode:anAddress.zipCode];
	someTimeReports = [self getTimeReportsByWorkID:anID];
	someChecklistPoints = [self getChecklistPointsByWorkID:anID];
	theTimeTypes = [self getTimeTypes];
	theStatusTypes = [self getStatusTypes];
	thePriorityTypes = [self getPriorityTypes];
	
	//Initialize the work order.
	WorkOrder *selectedWorkOrder = [[WorkOrder alloc] initWithWorkID:aWorkID 
		description:aDescription
		checklistType:aChecklistType 
		status:aStatus 
		priority:aPriority 
		customer:aCustomer 
		entryDate:theEntryDate 
		endDate:theEndDate 
		address:anAddress 
		employees:someEmployees 
		contactPersons:someContactPersons 
		workLeaders:someWorkLeaders 
		attachedFiles:someAttachedFiles 
		historyEntries:someHistoryEntries 
		timeReports:someTimeReports
		checklistPoints:someChecklistPoints
		timeTypes:theTimeTypes
		statusTypes:theStatusTypes
		priorityTypes:thePriorityTypes];
	
	//Release objects.
	[anAddress release];
	[selectedWorkOrder autorelease];
	
	return selectedWorkOrder;
}

/* Get the time reports for the selected work order */
- (NSMutableArray *) getTimeReportsByWorkID:(NSInteger)anID 
{
	//Init the array for storing read data.
	NSMutableArray *timeReports = [[NSMutableArray alloc] init];
	
	//Create the statement.
	NSString *sqlQuery = [NSString stringWithFormat: @"SELECT * FROM timereport WHERE workorder = %i ORDER BY reportDate desc", anID];
	const char *sqlStatement = [sqlQuery UTF8String];
	sqlite3_stmt *compiledStatement;
	
	//Handle errors.
	if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK)
	{
		//Loop through the returned rows.
		while(sqlite3_step(compiledStatement) == SQLITE_ROW)
		{
			//Read the data from the current row.
			NSString *anEntryDate = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement,0)];
			NSString *aComment = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement,1)];
			NSString *aSocialID = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement,2)];
			
			//NSString to NSDate formatting.
			NSDateFormatter *myDateFormatter = [[NSDateFormatter alloc] init];
			[myDateFormatter setDateFormat:@"yyyy-MM-dd HH:mm:SS"];
			NSDate *theEntryDate = [myDateFormatter dateFromString: anEntryDate];
			[myDateFormatter release];
			
			//Get author.
			WOPerson *currentAuthor = [self getPersonBySocialID:aSocialID];
			
			//Get time intervals.
			NSMutableArray *someTimeIntervals = [self getTimeIntervalsByWorkID:anID reportDate:theEntryDate];
			
			//Initialize time report.
			WOTimeReport *currentTimeReport = [[WOTimeReport alloc] initWithEntryDate:theEntryDate comment:aComment 
				author:currentAuthor timeIntervals:someTimeIntervals];
			
			[timeReports addObject:currentTimeReport];
			[currentTimeReport release];
		}
	}
	//Release the compiled statement from memory.
	sqlite3_finalize(compiledStatement);
	
	[timeReports autorelease];
	
	return timeReports;
};

/* Get the time intervals for a time report */
- (NSMutableArray *) getTimeIntervalsByWorkID:(NSInteger)anID reportDate:(NSDate *)anEntryDate
{
	//Init the array for storing read data.
	NSMutableArray *timeIntervals = [[NSMutableArray alloc] init];
	
	NSDateFormatter *myDateFormatter = [[NSDateFormatter alloc] init];
	[myDateFormatter setDateFormat:@"yyyy-MM-dd HH:mm:SS"];
	NSString *theEntryDate = [myDateFormatter stringFromDate:anEntryDate];
	[myDateFormatter release];
	
	//Create the statement.
	NSString *sqlQuery = [NSString stringWithFormat: @"SELECT * FROM hasTime WHERE workorder = %i AND reportDate ='%@'", anID, theEntryDate];
	const char *sqlStatement = [sqlQuery UTF8String];
	sqlite3_stmt *compiledStatement;
	
	//Handle errors.
	if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK)
	{
		//Loop through the returned rows.
		while(sqlite3_step(compiledStatement) == SQLITE_ROW)
		{
			//Read the data from the current row.
			NSString *aTimeType = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement,2)];
			NSTimeInterval someSeconds = (NSTimeInterval) sqlite3_column_double(compiledStatement,3);
			NSString *aStartTime = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement,4)];
			NSString *aStopTime = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement,5)];
			
			//NSString to NSDate formatting.
			NSDateFormatter *myDateFormatter = [[NSDateFormatter alloc] init];
			[myDateFormatter setDateFormat:@"yyyy-MM-dd HH:mm:SS"];
			
			NSDate *theStartTime = [myDateFormatter dateFromString: aStartTime];
			NSDate *theStopTime = [myDateFormatter dateFromString: aStopTime];
			[myDateFormatter release];
			
			//Initialize time interval.
			WOTimeInterval *currentTimeInterval = [[WOTimeInterval alloc] initWithTimeType:aTimeType 
																				   seconds:someSeconds 
																				 startTime:theStartTime 
																				  stopTime:theStopTime];
			[timeIntervals addObject:currentTimeInterval];
			[currentTimeInterval release];
		}
	}
	//Release the compiled statement from memory.
	sqlite3_finalize(compiledStatement);
	
	[timeIntervals autorelease];
	
	return timeIntervals;
}

/* Get the attached files for the selected work order */
- (NSMutableArray *) getAttachedFilesByWorkID:(NSInteger)anID 
{
	//Init the array for storing read data.
	NSMutableArray *attachedFiles = [[NSMutableArray alloc] init];
	
	//Create the statement.
	NSString *sqlQuery = [NSString stringWithFormat: @"SELECT * FROM hasFiles WHERE workorder = %i", anID];
	const char *sqlStatement = [sqlQuery UTF8String];
	sqlite3_stmt *compiledStatement;
	
	//Handle errors.
	if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK)
	{
		//Loop through the returned rows.
		while(sqlite3_step(compiledStatement) == SQLITE_ROW)
		{
			//Read the data from the current row.
			NSString *aFilePath = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement,1)];
			NSString *aFileName = [[aFilePath componentsSeparatedByString: @"/"] lastObject];
			
			//Create, store and release a new instance of AttachedFile.
			WOFile *currentFile = [[WOFile alloc] initWithFileName:aFileName fileURLPath:aFilePath];
			[attachedFiles addObject:currentFile];
			[currentFile release];
		}
	}
	//Release the compiled statement from memory.
	sqlite3_finalize(compiledStatement);
	
	[attachedFiles autorelease];
	
	return attachedFiles;
};

/* Get the history entries for the selected work order */
- (NSMutableArray *) getHistoryEntriesByStreet:(NSString *)aStreet zipCode:(NSString *)aZipCode  
{
	/* GET OLD ADDRESS NOTES */

	//Init the array for storing read data.
	NSMutableArray *historyEntries = [[NSMutableArray alloc] init];
	
	//Create the statement.
	NSString *firstQuery = [NSString stringWithFormat: @"SELECT * FROM note WHERE street = '%@' AND zip = '%@' ORDER BY entryDate desc", aStreet, aZipCode];
	const char *firstStatement = [firstQuery UTF8String];
	sqlite3_stmt *firstCompiledStatement;
	
	//Handle errors.
	if(sqlite3_prepare_v2(database, firstStatement, -1, &firstCompiledStatement, NULL) == SQLITE_OK)
	{
		//Loop through the returned rows.
		while(sqlite3_step(firstCompiledStatement) == SQLITE_ROW)
		{
			//Read the data from the current row.
			NSString *anEntryDate = [NSString stringWithUTF8String:(char *)sqlite3_column_text(firstCompiledStatement,0)];
			NSString *aDescription = [NSString stringWithUTF8String:(char *)sqlite3_column_text(firstCompiledStatement,1)];
			NSString *anAuthor = [NSString stringWithUTF8String:(char *)sqlite3_column_text(firstCompiledStatement,2)];
			
			//NSString to NSDate formatting.
			NSDateFormatter *myDateFormatter = [[NSDateFormatter alloc] init];
			[myDateFormatter setDateFormat:@"yyyy-MM-dd HH:mm:SS"];
			NSDate *theEntryDate = [myDateFormatter dateFromString: anEntryDate];
			[myDateFormatter release];

			WOPerson *currentAuthor = [self getPersonBySocialID:anAuthor];
			
			//Initialize, add and release the current history entry.
			WOHistoryEntry *currentHistoryEntry = [[WOHistoryEntry alloc] initWithEntryDate:theEntryDate 
																				description:aDescription
																				  entryType:@"Note" 
																					 author:currentAuthor];
			[historyEntries addObject:currentHistoryEntry];
			[currentHistoryEntry release];
		}
	}
	//Release the compiled statement from memory.
	sqlite3_finalize(firstCompiledStatement);
	
	/* GET OLD WORK ORDER DESCRIPTIONS */
	
	//Set up the SQL statement and compile it for faster access.
	NSString *secondQuery = [NSString stringWithFormat: @"SELECT * FROM workorder WHERE status = 'Avslutad' AND addressStreet ='%@' AND addressZip = '%@'",
						  aStreet, aZipCode];
	const char *secondStatement = [secondQuery UTF8String];
	sqlite3_stmt *secondCompiledStatement;
	
	//Handle errors.
	if(sqlite3_prepare_v2(database, secondStatement, -1, &secondCompiledStatement, NULL) == SQLITE_OK)
	{
		//Loop through the returned rows.
		while(sqlite3_step(secondCompiledStatement) == SQLITE_ROW)
		{
			//Read the data from the current row.
			NSInteger aWorkID = (NSInteger)sqlite3_column_int(secondCompiledStatement,0);
			NSString *aDescription = [NSString stringWithUTF8String:(char *)sqlite3_column_text(secondCompiledStatement,1)];
			NSString *anEndDate = [NSString stringWithUTF8String:(char *)sqlite3_column_text(secondCompiledStatement,3)];
			
			NSString *finalDescription = [NSString stringWithFormat:@"%@\n\nOrdernummer: %i", aDescription, aWorkID];
			
			//NSString to NSDate formatting.
			NSDateFormatter *myDateFormatter = [[NSDateFormatter alloc] init];
			[myDateFormatter setDateFormat:@"yyyy-MM-dd HH:mm:SS"];
			NSDate *theEndDate = [myDateFormatter dateFromString: anEndDate];
			[myDateFormatter release];
			
			//Get the first work leader.
			NSMutableArray *workLeaders = [self getWorkLeadersByWorkID:aWorkID];
			WOPerson *theWorkLeader = [workLeaders objectAtIndex:0];
			
			//Initialize, add and release the current history entry.
			WOHistoryEntry *currentHistoryEntry = [[WOHistoryEntry alloc] initWithEntryDate:theEndDate
																				description:finalDescription
																				  entryType:@"Specification"  
																					 author:theWorkLeader];
			[historyEntries addObject:currentHistoryEntry];
			[currentHistoryEntry release];
		}
	}
	//Release the compiled statement from memory.
	sqlite3_finalize(secondCompiledStatement);
	
	[historyEntries autorelease];
	
	return historyEntries;
}

/* Get the text of a checklist point */
- (NSMutableArray *) getChecklistPointsByWorkID:(NSInteger)anID 
{
	//Init the array for storing read data.
	NSMutableArray *checklistPoints = [[NSMutableArray alloc] init];
	
	//Create the statement.
	NSString *sqlQuery = [NSString stringWithFormat: @"SELECT * FROM workorderChecklistInfo WHERE workorder = %i", anID];
	const char *sqlStatement = [sqlQuery UTF8String];
	sqlite3_stmt *compiledStatement;

	//Handle errors.
	if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK)
	{
		//Loop through the returned rows.
		while(sqlite3_step(compiledStatement) == SQLITE_ROW)
		{
			//Read the data from the current row.
			NSInteger anIndex = (NSInteger)sqlite3_column_int(compiledStatement,1);
			NSString *aText= [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 2)];
			NSInteger aBool = (NSInteger)sqlite3_column_int(compiledStatement,3);
			
			//NSInteger to BOOL
			BOOL isChecked;
			if(aBool == 1)
				isChecked = YES;
			else 
				isChecked = NO;
			
			//Initialize, add and release the current checklist point.
			WOChecklistPoint *currentChecklistPoint = [[WOChecklistPoint alloc]initWithIndex:anIndex text:aText checked:isChecked];
			[checklistPoints addObject:currentChecklistPoint];
			[currentChecklistPoint release];
		}
	}
	//Release the compiled statement from memory.
	sqlite3_finalize(compiledStatement);
	
	[checklistPoints autorelease];
	
	return checklistPoints;
}

/* Get the assigned employees for the selected work order */
- (NSMutableArray *) getEmployeesByWorkID:(NSInteger)anID
{
	//Init the array for storing read data.
	NSMutableArray *employees = [[NSMutableArray alloc] init];
	
	//Set up the SQL statement and compile it for faster access.
	NSString *sqlQuery = [NSString stringWithFormat: @"SELECT * FROM hasEmployees WHERE workorder = %i", anID];
	const char *sqlStatement = [sqlQuery UTF8String];
	sqlite3_stmt *compiledStatement;
	
	if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK)
	{
		//Loop through the returned rows.
		while(sqlite3_step(compiledStatement) == SQLITE_ROW)
		{
			//Read the data from the current row.
			NSString *aSocialID = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement,1)];
			
			WOPerson *currentEmployee = [self getPersonBySocialID:aSocialID];
			[employees addObject:currentEmployee];
		}
	}
	//Release the compiled statement from memory.
	sqlite3_finalize(compiledStatement);
	
	[employees autorelease];
	
	return employees;
}

/* Get the work leaders for the selected work order */
- (NSMutableArray *) getWorkLeadersByWorkID:(NSInteger)anID
{
	//Init the array for storing read data.
	NSMutableArray *workLeaders = [[NSMutableArray alloc] init];
		
	//Set up the SQL statement and compile it for faster access.
	NSString *sqlQuery = [NSString stringWithFormat: @"SELECT * FROM hasResponsibles WHERE workorder = %i", anID];
	const char *sqlStatement = [sqlQuery UTF8String];
	sqlite3_stmt *compiledStatement;
		
	if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK)
	{
		//Loop through the returned rows.
		while(sqlite3_step(compiledStatement) == SQLITE_ROW)
		{
			//Read the data from the current row.
			NSString *aSocialID = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement,1)];
			
			WOPerson *currentWorkLeader = [self getPersonBySocialID:aSocialID];
			[workLeaders addObject:currentWorkLeader];
		}
	}
	//Release the compiled statement from memory.
	sqlite3_finalize(compiledStatement);
	
	[workLeaders autorelease];
	
	return workLeaders;
}

/* Get the contact persons for the selected work order */
- (NSMutableArray *) getContactPersonsByWorkID:(NSInteger)anID
{
	//Init the array for storing read data.
	NSMutableArray *contactPersons = [[NSMutableArray alloc] init];
	
	//Create the statement.
	NSString *sqlQuery = [NSString stringWithFormat: @"SELECT * FROM hasContacts WHERE workorder = %i", anID];
	const char *sqlStatement = [sqlQuery UTF8String];
	sqlite3_stmt *compiledStatement;
	
	//Handle errors.
	if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK)
	{
		//Loop through the returned rows.
		while(sqlite3_step(compiledStatement) == SQLITE_ROW)
		{
			//Read the data from the current row.
			NSString *aSocialID = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement,1)];
			WOPerson *currentContactPerson = [self getPersonBySocialID:aSocialID];
			[contactPersons addObject:currentContactPerson];
		}
	}
	//Release the compiled statement from memory.
	sqlite3_finalize(compiledStatement);
	
	[contactPersons autorelease];
	
	return contactPersons;
}

#pragma mark -

#pragma mark Creater Methods

/* Creates a new work order */
- (void) createNewWorkOrder:(WorkOrder *)aNewWorkOrder
{
	//Create address if it doesn't exist
	WOAddress *readAddress = [self getAddressIfExists:aNewWorkOrder.address];
	if (readAddress == nil) 
	{
		[self createNewAddress:aNewWorkOrder.address];
		readAddress = aNewWorkOrder.address;
	}	
	
	//Create the statement.
	NSString *sqlQuery = [NSString stringWithFormat: @"INSERT INTO workorder VALUES(%@, '%@', %@, %@, '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@')",
						  @"NULL",
						  aNewWorkOrder.description,
						  @"strftime('%Y-%m-%d %H:%M:%S', 'now')",
						  @"strftime('%Y-%m-%d %H:%M:%S', '2010-06-10 13:37:00')",
						  aNewWorkOrder.customer, 
						  aNewWorkOrder.status,
						  aNewWorkOrder.priority,
						  aNewWorkOrder.checklistType,
						  readAddress.street,
						  readAddress.zipCode,
						  readAddress.area,
						  readAddress.city];
	
	const char *sqlStatement = [sqlQuery UTF8String];
	sqlite3_stmt *compiledStatement;
	
	//Handle errors.
	if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK)
	{
		//Perform the statement.
		sqlite3_step(compiledStatement);
		
		//Get the created workID of the work order just inserted.
		NSInteger newWorkID = sqlite3_last_insert_rowid(database);
		
		//Set this workID to the object.
		aNewWorkOrder.workID = newWorkID;
		
		[self setTimeReports:aNewWorkOrder];
		[self setHistoryEntries:aNewWorkOrder];
		[self setEmployees:aNewWorkOrder];
		[self setContactPersons:aNewWorkOrder];
		[self setWorkLeaders:aNewWorkOrder];
		
		//Create and set checklist points.
		aNewWorkOrder.checklistPoints = [self createChecklistPoints:aNewWorkOrder];
		[self setChecklistPoints:aNewWorkOrder];
	}
	//Release the compiled statement from memory.
	sqlite3_finalize(compiledStatement);
}

/* Creates a new customer */
- (void) createNewCustomer:(NSString *)aNewCustomer;
{
	//Create the statement.
	NSString *sqlQuery = [NSString stringWithFormat: @"INSERT INTO customer VALUES('%@')", aNewCustomer];
	const char *sqlStatement = [sqlQuery UTF8String];
	sqlite3_stmt *compiledStatement;
	
	//Handle errors.
	if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK)
	{
		//Perform the statement.
		sqlite3_step(compiledStatement);
	}
	//Release the compiled statement from memory.
	sqlite3_finalize(compiledStatement);
}

/* Creates a new address */
- (void) createNewAddress:(WOAddress *)aNewAddress
{
	//Create the statement.
	NSString *sqlQuery = [NSString stringWithFormat: @"INSERT INTO address VALUES('%@','%@','%@','%@','%@')",
						  aNewAddress.street,
						  aNewAddress.zipCode,
						  aNewAddress.area,
						  aNewAddress.city];
	const char *sqlStatement = [sqlQuery UTF8String];
	sqlite3_stmt *compiledStatement;
	
	//Handle errors.
	if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK)
	{
		//Perform the statement.
		sqlite3_step(compiledStatement);
	}
	//Release the compiled statement from memory.
	sqlite3_finalize(compiledStatement);
}

/* Creates a new WOPerson */
- (void) createNewPerson:(WOPerson *)aNewPerson
{
	//Create the statement.
	NSString *sqlQuery = [NSString stringWithFormat: @"INSERT INTO people VALUES('%@','%@','%@','%@','%@')",
						  aNewPerson.socialID, 
						  aNewPerson.firstName,
						  aNewPerson.lastName,
						  aNewPerson.email,
						  aNewPerson.phone];
	const char *sqlStatement = [sqlQuery UTF8String];
	sqlite3_stmt *compiledStatement;
	
	//Handle errors.
	if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK)
	{
		//Perform the statement.
		sqlite3_step(compiledStatement);
	}
	//Release the compiled statement from memory.
	sqlite3_finalize(compiledStatement);
}

/* Updates the checklist points for the selected work order */
- (NSMutableArray *) createChecklistPoints:(WorkOrder *)aWorkOrder
{
	//NSMutableArrays for storing data.
	NSMutableArray *theChecklistPoints = [[NSMutableArray alloc]init];
	
	//Create the statement.
	NSString *sqlQuery = [NSString stringWithFormat: @"SELECT * FROM generalChecklistInfo WHERE checklist = '%@'", aWorkOrder.checklistType];
	const char *sqlStatement = [sqlQuery UTF8String];
	sqlite3_stmt *compiledStatement;
	
	//Handle errors.
	if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK)
	{
		//Loop through the returned rows.
		while(sqlite3_step(compiledStatement) == SQLITE_ROW)
		{
			NSInteger anIndex = (NSInteger)sqlite3_column_int(compiledStatement, 1);
			NSString *aText = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 2)];
			
			WOChecklistPoint *newChecklistPoint = [[WOChecklistPoint alloc]initWithIndex:anIndex text:aText checked:NO];
			[theChecklistPoints addObject:newChecklistPoint];
			[newChecklistPoint release];
		}
	}
	//Release the compiled statement from memory.
	sqlite3_finalize(compiledStatement);
	
	[theChecklistPoints autorelease];
	
	return theChecklistPoints;
}

#pragma mark -

#pragma mark Update Methods

/* Updates the current work order */
- (void) updateWorkOrder:(WorkOrder *)aWorkOrder
{
	//Create the statement.
	NSString *sqlQuery = [NSString stringWithFormat: @"UPDATE workorder SET status = '%@' WHERE id =%i",
						  aWorkOrder.status,
						  aWorkOrder.workID];
	const char *sqlStatement = [sqlQuery UTF8String];
	sqlite3_stmt *compiledStatement;
	
	//Handle errors.
	if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK)
	{
		//Perform the statement.
		sqlite3_step(compiledStatement);
		
		[self setTimeReports:aWorkOrder];
		[self updateTimeReports:aWorkOrder];
		[self setHistoryEntries:aWorkOrder];
		[self updateHistoryEntries:aWorkOrder];
		[self setEmployees:aWorkOrder];
		[self setContactPersons:aWorkOrder];
		[self setWorkLeaders:aWorkOrder];
		[self setChecklistPoints:aWorkOrder];
	}
	//Release the compiled statement from memory.
	sqlite3_finalize(compiledStatement);
}

/* Updates the time reports for the selected work order */
- (void) updateTimeReports:(WorkOrder *)aWorkOrder
{	
	//Format NSDate to NSString.
	NSDateFormatter *myDateFormatter = [[NSDateFormatter alloc] init];
	[myDateFormatter setDateFormat:@"yyyy-MM-dd HH:mm:SS"];
	
	//Insert new entries.
	for (WOTimeReport *currentTimeReport in aWorkOrder.updatedTimeReports) 
	{	
		NSString *theReportDate = [myDateFormatter stringFromDate:currentTimeReport.entryDate];
		
		//Create the statement.
		NSString *sqlQuery = [NSString stringWithFormat: @"UPDATE timereport SET comment = '%@' WHERE reportDate = '%@' AND workorder = %i",
							  currentTimeReport.comment,
							  theReportDate,
							  aWorkOrder.workID];
		const char *sqlStatement = [sqlQuery UTF8String];
		sqlite3_stmt *compiledStatement;
		
		//Handle errors.
		if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK)
		{
			//Perform the statement.
			sqlite3_step(compiledStatement);
		}
		//Release the compiled statement from memory.
		sqlite3_finalize(compiledStatement);
		
		//Set the time intervals of the time report.
		[self updateTimeIntervalsByTimeReport:currentTimeReport workorder:aWorkOrder];
	}
	[myDateFormatter release];	
}

/* Updates the time intervals for a time report */
- (void) updateTimeIntervalsByTimeReport:(WOTimeReport *)aTimeReport workorder:(WorkOrder *)aWorkOrder
{
	/* DELETE ALL THE OLD VALUES */
	
	//Format NSDate to NSString.
	NSDateFormatter *myDateFormatter = [[NSDateFormatter alloc] init];
	[myDateFormatter setDateFormat:@"yyyy-MM-dd HH:mm:SS"];
	NSString *theReportDate = [myDateFormatter stringFromDate:aTimeReport.entryDate];
	[myDateFormatter release];
	
	//Create the statement.
	NSString *sqlQuery = [NSString stringWithFormat: @"DELETE FROM hasTime WHERE workorder = %i AND reportDate = '%@'", aWorkOrder.workID, theReportDate];
	const char *sqlStatement = [sqlQuery UTF8String];
	sqlite3_stmt *compiledStatement;
	
	//Handle errors.
	if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK)
	{
		//Perform the statement.
		sqlite3_step(compiledStatement);
	}
	
	//Release the compiled statement from memory.
	sqlite3_finalize(compiledStatement);
	
	/* SET ALL THE VALUES AGAIN */
	
	[self setTimeIntervalsByTimeReport: aTimeReport workorder:aWorkOrder];
}

/* Update modified history entries */
- (void) updateHistoryEntries:(WorkOrder *)aWorkOrder
{
	//Format NSDate to NSString.
	NSDateFormatter *myDateFormatter = [[NSDateFormatter alloc] init];
	[myDateFormatter setDateFormat:@"yyyy-MM-dd HH:mm:SS"];

	for(WOHistoryEntry *currentHistoryEntry in aWorkOrder.updatedHistoryEntries)
	{
		NSString *theEntryDate = [myDateFormatter stringFromDate:currentHistoryEntry.entryDate];
		//Create the statement.
		NSString *sqlQuery = [NSString stringWithFormat: @"UPDATE note SET txt = '%@' WHERE entryDate = '%@' AND street = '%@' AND zip = '%@'",
							  currentHistoryEntry.description,
							  theEntryDate,
							  aWorkOrder.address.street,
							  aWorkOrder.address.zipCode];
		const char *sqlStatement = [sqlQuery UTF8String];
		sqlite3_stmt *compiledStatement;
		
		//Handle errors.
		if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK)
		{
			//Perform the statement.
			sqlite3_step(compiledStatement);
		}
		//Release the compiled statement from memory.
		sqlite3_finalize(compiledStatement);
	}
	[myDateFormatter release];
}

#pragma mark -

#pragma mark Setter Methods

/* Sets the new time reports for the selected work order */
- (void) setTimeReports:(WorkOrder *)aWorkOrder
{
	NSMutableArray *newTimeReports = aWorkOrder.addedTimeReports;
	
	//Format NSDate to NSString.
	NSDateFormatter *myDateFormatter = [[NSDateFormatter alloc] init];
	[myDateFormatter setDateFormat:@"yyyy-MM-dd HH:mm:SS"];
	
	//Get the current user
	WOPrototypeAppDelegate *appDelegate = (WOPrototypeAppDelegate *)[[UIApplication sharedApplication] delegate];
	WOPerson *currentUser = appDelegate.currentUser;
	
	//Insert new entries.
	for (WOTimeReport *currentTimeReport in newTimeReports) 
	{	
		NSString *theEntryDate = [myDateFormatter stringFromDate:currentTimeReport.entryDate];
		
		//Create the statement.
		NSString *sqlQuery = [NSString stringWithFormat: @"INSERT INTO timereport VALUES('%@','%@','%@',%i)",
							  theEntryDate,
							  currentTimeReport.comment,
							  currentUser.socialID,
							  aWorkOrder.workID];
		const char *sqlStatement = [sqlQuery UTF8String];
		sqlite3_stmt *compiledStatement;
		
		//Handle errors.
		if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK)
		{
			//Perform the statement.
			sqlite3_step(compiledStatement);
		}
		//Release the compiled statement from memory.
		sqlite3_finalize(compiledStatement);
		
		//Set the time intervals of the time report.
		[self setTimeIntervalsByTimeReport:currentTimeReport workorder:aWorkOrder];
	}
	[myDateFormatter release];	
}

/* Sets the time intervals for a new time report */
- (void) setTimeIntervalsByTimeReport:(WOTimeReport *)aTimeReport workorder:(WorkOrder *)aWorkOrder
{	
	//Format NSDate to NSString
	NSDateFormatter *myDateFormatter = [[NSDateFormatter alloc] init];
	[myDateFormatter setDateFormat:@"yyyy-MM-dd HH:mm:SS"];
	
	/* Insert the new entries */
	for (WOTimeInterval	*currentTimeInterval in aTimeReport.timeIntervals) 
	{	
		NSDate *startTime = [NSDate date];
		NSDate *stopTime = [NSDate date];
		NSString *theEntryDate = [myDateFormatter stringFromDate:aTimeReport.entryDate];
		NSString *theStartTime = [myDateFormatter stringFromDate:startTime];
		NSString *theStopTime = [myDateFormatter stringFromDate:stopTime];
		NSTimeInterval theSeconds = currentTimeInterval.seconds;
		//Create the statement.
		NSString *sqlQuery = [NSString stringWithFormat: @"INSERT INTO hasTime VALUES('%@',%i,'%@',%f,'%@','%@')",
							 theEntryDate,
							 aWorkOrder.workID,
							 currentTimeInterval.timeType,
							 theSeconds,
							 theStartTime,
							 theStopTime];
		const char *sqlStatement = [sqlQuery UTF8String];
		sqlite3_stmt *compiledStatement;
		
		//Handle errors.
		if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK)
		{
			//Perform the statement.
			sqlite3_step(compiledStatement);
		}
		//Release the compiled statement from memory.
		sqlite3_finalize(compiledStatement);
	}	
	[myDateFormatter release];	
}

/* Updates the history entries for the selected work order */
- (void) setHistoryEntries:(WorkOrder *)aWorkOrder
{	
	//Format NSDate to NSString
	NSDateFormatter *myDateFormatter = [[NSDateFormatter alloc] init];
	[myDateFormatter setDateFormat:@"yyyy-MM-dd HH:mm:SS"];
	
	//Get the current user
	WOPrototypeAppDelegate *appDelegate = (WOPrototypeAppDelegate *)[[UIApplication sharedApplication] delegate];
	WOPerson *currentUser = appDelegate.currentUser;
	
	/* Insert the new entries */
	for (WOHistoryEntry	*currentHistoryEntry in aWorkOrder.addedHistoryEntries) 
	{	
		NSString *theEntryDate = [myDateFormatter stringFromDate:currentHistoryEntry.entryDate];
		
		//Create the statement.
		NSString *sqlQuery = [NSString stringWithFormat: @"INSERT INTO note VALUES('%@','%@','%@','%@','%@')",
							  theEntryDate,
							  currentHistoryEntry.description,
							  currentUser.socialID, 
							  aWorkOrder.address.street,
							  aWorkOrder.address.zipCode];
		const char *sqlStatement = [sqlQuery UTF8String];
		sqlite3_stmt *compiledStatement;
		
		//Handle errors.
		if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK)
		{
			//Perform the statement.
			sqlite3_step(compiledStatement);
		}
		//Release the compiled statement from memory.
		sqlite3_finalize(compiledStatement);
	}
	[myDateFormatter release];
}

/* Sets the checklist points for the selected work order */
- (void) setChecklistPoints:(WorkOrder *)aWorkOrder
{
	//Get the checklistpoints.
	NSMutableArray *modifiedChecklistPoints = aWorkOrder.checklistPoints;
	
	/* Delete the old entries */
	//Create the statement.
	NSString *sqlQuery = [NSString stringWithFormat: @"DELETE FROM hasChecked WHERE workorder = %i", aWorkOrder.workID];
	const char *sqlStatement = [sqlQuery UTF8String];
	sqlite3_stmt *compiledStatement;
	
	//Handle errors.
	if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK)
	{
		//Perform the statement.
		sqlite3_step(compiledStatement);
	}
	
	//Release the compiled statement from memory.
	sqlite3_finalize(compiledStatement);
	
	/* Insert the new entries */
	for (WOChecklistPoint *currentChecklistPoint in modifiedChecklistPoints) 
	{	
		//BOOL to NSInteger.
		NSInteger check;
		if (currentChecklistPoint.checked == YES) 
			check = 1;
		else
			check = 0;
		
		//Create the statement.
		NSString *sqlQuery = [NSString stringWithFormat: @"INSERT INTO hasChecked VALUES(%i,%i,%i)",
							  aWorkOrder.workID, currentChecklistPoint.nr, check];
		const char *sqlStatement = [sqlQuery UTF8String];
		sqlite3_stmt *compiledStatement;
		
		//Handle errors.
		if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK)
		{
			//Perform the statement.
			sqlite3_step(compiledStatement);
		}
		//Release the compiled statement from memory.
		sqlite3_finalize(compiledStatement);
	}
}

/* Updates the assigned employees for the selected work order */
- (void) setEmployees:(WorkOrder *)aWorkOrder
{
	//get the assigned employees.
	NSMutableArray *modifiedEmployees = aWorkOrder.employees;
	
	/* Delete the old entries */
	//Create the statement.
	NSString *sqlQuery = [NSString stringWithFormat: @"DELETE FROM hasEmployees WHERE workorder = %i", aWorkOrder.workID];
	const char *sqlStatement = [sqlQuery UTF8String];
	sqlite3_stmt *compiledStatement;
	
	//Handle errors.
	if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK)
	{
		//Perform the statement.
		sqlite3_step(compiledStatement);
	}
			
	//Release the compiled statement from memory.
	sqlite3_finalize(compiledStatement);
	
	/* Insert the new entries */
	for (WOPerson *currentEmployee in modifiedEmployees) 
	{	
		if(![self doesPersonExist:currentEmployee])
		{
			[self createNewPerson:currentEmployee];
		}
		
		//Create the statement.
		NSString *sqlQuery = [NSString stringWithFormat: @"INSERT INTO hasEmployees VALUES(%i,'%@')",
			aWorkOrder.workID, currentEmployee.socialID];
		const char *sqlStatement = [sqlQuery UTF8String];
		sqlite3_stmt *compiledStatement;
		
		//Handle errors.
		if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK)
		{
			//Perform the statement.
			sqlite3_step(compiledStatement);
		}
		//Release the compiled statement from memory.
		sqlite3_finalize(compiledStatement);
	}
}

/* Updates the work leaders for the selected work order */
- (void) setWorkLeaders:(WorkOrder *)aWorkOrder
{
	//Get the assigned work leaders.
	NSMutableArray *modifiedWorkLeaders = aWorkOrder.workLeaders;
	
	/* Delete the old entries */
	//Create the statement.
	NSString *sqlQuery = [NSString stringWithFormat: @"DELETE FROM hasResponsibles WHERE workorder = %i", aWorkOrder.workID];
	const char *sqlStatement = [sqlQuery UTF8String];
	sqlite3_stmt *compiledStatement;
	
	//Handle errors.
	if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK)
	{
		//Perform the statement.
		sqlite3_step(compiledStatement);
	}
		
	//Release the compiled statement from memory.
	sqlite3_finalize(compiledStatement);
	
	//Insert the new entries.
	for (WOPerson *currentWorkLeader in modifiedWorkLeaders) 
	{	
		if(![self doesPersonExist:currentWorkLeader])
		{
			[self createNewPerson:currentWorkLeader];
		}
	
		//Create the statement.
		NSString *sqlQuery = [NSString stringWithFormat: @"INSERT INTO hasResponsibles VALUES(%i,'%@')",
							  aWorkOrder.workID, currentWorkLeader.socialID];
		const char *sqlStatement = [sqlQuery UTF8String];
		sqlite3_stmt *compiledStatement;
		
		//Handle errors.
		if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK)
		{
			//Perform the statement.
			sqlite3_step(compiledStatement);
		}
		//Release the compiled statement from memory.
		sqlite3_finalize(compiledStatement);
	}	
}

/* Updates the contact persons for the selected work order */
- (void) setContactPersons:(WorkOrder *)aWorkOrder
{
	//Get the assigned contact persons.
	NSMutableArray *modifiedContactPersons = aWorkOrder.contactPersons;
	
	/* Delete the old entries */
	//Create the statement.
	NSString *sqlQuery = [NSString stringWithFormat: @"DELETE FROM hasContacts WHERE workorder = %i", aWorkOrder.workID];
	const char *sqlStatement = [sqlQuery UTF8String];
	sqlite3_stmt *compiledStatement;
	
	//Handle errors.
	if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK)
	{
		//Perform the statement.
		sqlite3_step(compiledStatement);
	}
	
	//Release the compiled statement from memory.
	sqlite3_finalize(compiledStatement);
	
	/* Insert the new entries */
	for (WOPerson *currentContactPerson in modifiedContactPersons) 
	{	
		if(![self doesPersonExist:currentContactPerson])
		{
			[self createNewPerson:currentContactPerson];
		}
	
		//Create the statement.
		NSString *sqlQuery = [NSString stringWithFormat: @"INSERT INTO hasContacts VALUES(%i,'%@')",
							  aWorkOrder.workID, currentContactPerson.socialID];
		const char *sqlStatement = [sqlQuery UTF8String];
		sqlite3_stmt *compiledStatement;
		
		//Handle errors.
		if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK)
		{
			//Perform the statement.
			sqlite3_step(compiledStatement);
		}
		//Release the compiled statement from memory.
		sqlite3_finalize(compiledStatement);
	}
}

#pragma mark -

@end